Redshiftのグループ作成と権限付与について

こんにちは、データアナリティクス事業本部@那覇オフィスの下地です。

redshiftでは、グループを作成して権限を管理することができるので、複数のユーザーを管理するにはとても便利です。

しかし、意図した通りの権限を付与する際にハマりましたので内容についてまとめたいと思います。

1. 全体像

今回、グループに所属したユーザー1/2を作成し、ユーザー1が作成したテーブルにユーザー2がSELECT権限を有していることの確認をゴールとします。 また、想定通りに権限が付与できなかったパターンを紹介します。

権限を確認するための実行手順と作成する項目の表を記載します。

1. グループとユーザー作成
2. ユーザーの権限を確認
3. グループへの権限付与
4. ユーザー1でテーブルを作成し権限を確認
5. デフォルト権限の設定がうまくいかなったパターン

#	項目	名称	備考
1	グループ	test_group_1
2	ユーザー	test_user1_1
3	ユーザー	test_user1_2
4	スキーマ	schema_test_1
5	テーブル	test_from_root	スーパーユーザーで作成
6	テーブル	test_from_user1	ユーザー1で作成

2. 権限について

実施前にグループへの権限付与に関する項目について確認します。

2.1 GRANT

作成したグループへの権限付与はGRANTを使用します。 付与する権限はCREATE/USAGE/SELECTになりますので該当部分の構文を記載します。

GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]
...
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { username [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

2.2 ALTER DEFAULT PRIVILEGES

続いてALTER DEFAULT PRIVILEGESを確認します。 GRANTでSELECTを権限を付与しておりますがこれは既存テーブルに対するものとなります。 そのため、異なるユーザーが新規に作成するオブジェクトに対してのデフォルト権限を設定する必要があります。

今回作成するユーザー1とユーザー2は同じグループに所属しておりますが、ユーザー1が作成したテーブルに対してのSELECT権限をユーザー2に与えるには GROUPにデフォルト権限を付与する必要がありますので、該当部分の構文を記載します。

ALTER DEFAULT PRIVILEGES
    [ FOR USER target_user [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]

GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] } 
    ON TABLES 
    TO { user_name [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]  

3. 作ってみる

では実際に試してみます。

3.1 グループとユーザー作成

まずはじめにスーパーユーザーにてグループと所属するユーザーを2つ作成します。 その後、スキーマとスーパーユーザーにてテーブルを1つ作成します。

-- グループ作成
CREATE GROUP test_group_1;

-- グループに所属したユーザー1/2を作成
CREATE USER test_user1_1 WITH PASSWORD '***' in group test_group_1;
CREATE USER test_user1_2 WITH PASSWORD '***' in group test_group_1;

-- テスト用のスキーマを作成
CREATE SCHEMA schema_test_1;

-- スーパーユーザーにてテーブルを作成(グループへの権限付与前)
create table schema_test_1.test_from_root(id int);

3.2 ユーザーの権限を確認

AWS Redshift ユーザ単位で各テーブルに対する権限の有無を確認してみたを参考に作成したユーザーの権限を確認するクエリを実行し結果を表にまとめます。

select
  usename
  , schemaname
  , tablename
  , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select
  , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert
  , has_schema_privilege(usename, schemaname, 'usage') as usage
from
  pg_tables, pg_user
where schemaname in ('schema_test_1')            -- 確認対象スキーマ
and usename in ('test_user1_1', 'test_user1_2')  -- 確認対象ユーザ
order by 1, 2, 3;

usename	schemaname	tablename	select	insert	usage
test_user1_1	schema_test_1	test_from_root
test_user1_2	schema_test_1	test_from_root

グループを作成してユーザーを紐付けたのみでは、スーパーユーザーが作成したテーブルに権限がないことが確認できます。

3.3 グループへの権限付与

それでは、グループに以下の権限を付与します。

• a. 指定スキーマに対してUSAGE, CREATE権限
• b. 指定スキーマの既存テーブルに対してのSELECT権限
• c. 指定したユーザーが作成するスキーマに配置したオブジェクトに対して、SELECTのデフォルト権限

-- a
GRANT USAGE, CREATE 
ON SCHEMA schema_test_1 
TO GROUP test_group_1;

-- b
GRANT SELECT ON ALL TABLES 
IN SCHEMA schema_test_1 
TO GROUP test_group_1;

-- c
ALTER DEFAULT PRIVILEGES 
FOR USER test_user1_1, test_user1_2 
IN SCHEMA schema_test_1 
GRANT SELECT
ON tables TO group test_group_1;

3.4 ユーザー1でテーブルを作成し権限を確認

権限付与後に、ユーザー1でテーブルを作成します。

-- ユーザー1にてテーブルを作成
create table schema_test_1.test_from_user1(id int);

続いて3.2で使用したクエリを実行してユーザーの権限を改めて確認します。

usename	schemaname	tablename	select	insert	usage
test_user1_1	schema_test_1	test_from_root	◯		◯
test_user1_2	schema_test_1	test_from_root	◯		◯
test_user1_1	schema_test_1	test_from_user1	◯	◯	◯
test_user1_2	schema_test_1	test_from_user1	◯		◯

スーパーユーザーが作成したテーブルに対してSELECT/USAGE権限が付与されていること、ユーザー1が作成したテーブルにユーザー2にもSELECT権限のみ付与されいていることが確認できました!

3.5 デフォルト権限の設定がうまくいかなったパターン

続いて、ユーザー1が作成したテーブルに対してユーザー2のSELECTデフォルト権限が付与できなかったパターンを紹介します。

2.2でも記載しましたが、ユーザー1とユーザー2は異なります。 しかし、同じGROUPに所属しているのでFOR USERの指定は不要だと思い設定しなかったために、ユーザー1が作成したテーブルに対する権限が付与できませんでした。

コードとしては3.3のFOR USER部分が未設定になるため該当部分をコメントアウトして3.1~3.4の内容を実行します。

-- c. 指定したユーザーが作成するスキーマに配置したオブジェクトに対して、SELECTのデフォルト権限を付与
ALTER DEFAULT PRIVILEGES 
-- FOR USER test_user1_1, test_user1_2
IN SCHEMA schema_test_1 
GRANT SELECT
ON tables TO group test_group_1;

上記を実行した結果を表にまとめます。先ほどと違い、ユーザー1が作成したテーブルにユーザー2のSELECT権限がないことがわかります。

usename	schemaname	tablename	select	insert	usage
test_user1_1	schema_test_1	test_from_root	◯		◯
test_user1_2	schema_test_1	test_from_root	◯		◯
test_user1_1	schema_test_1	test_from_user1	◯	◯	◯
test_user1_2	schema_test_1	test_from_user1			◯

デフォルト権限は、指定したユーザーが作ったテーブルに対する権限の付与になりますので私はここでハマりました。。

今回のケースではグループに対する権限になりますが、Redshiftのデフォルト権限設定でハマったことではユーザーに対しての権限付与に関してまとめてられておりますので合わせて読んでいただけたらと思います。

3. まとめ

権限周りの設定はふとしたところにハマりポイントがあるなと感じましたので、グループ作成と権限付与をまとめました。 この記事がどなたかの助けになれば幸いです。

4. 参考リンク

• ユーザーおよびグループのアクセス権限の管理例
• グループ
• GRANT
• ALTER DEFAULT PRIVILEGES
• AWS Redshift ユーザ単位で各テーブルに対する権限の有無を確認してみた
• Amazon Redshift グループとユーザー、アクセス権の管理について
• Redshiftのデフォルト権限設定でハマったこと